#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
从Excel读取数据并在视频上添加文本和图标
该脚本可以直接运行，用于根据Excel数据在视频上叠加文本和图标
"""

import pandas as pd
import os
import sys
import base64
import random
from pathlib import Path

# 尝试导入moviepy.editor，如果失败则给出提示
try:
    from moviepy.editor import VideoFileClip, TextClip, CompositeVideoClip, ImageClip
    from moviepy.video.tools.drawing import color_gradient
    MOVIEPY_AVAILABLE = True
except ImportError as e:
    print(f"无法导入moviepy.editor: {e}")
    print("请确保已正确安装moviepy: pip install moviepy==2.2.1")
    MOVIEPY_AVAILABLE = False

# 尝试导入PIL，如果失败则给出提示
try:
    from PIL import Image, ImageDraw, ImageFont
    PIL_AVAILABLE = True
except ImportError as e:
    print(f"无法导入PIL: {e}")
    print("请确保已正确安装Pillow: pip install Pillow")
    PIL_AVAILABLE = False


def read_excel_data(excel_path):
    """
    读取Excel文件中的比赛信息
    
    Args:
        excel_path (str): Excel文件路径
        
    Returns:
        list: 包含比赛信息的字典列表
    """
    try:
        # 读取Excel文件
        df = pd.read_excel(excel_path)
        
        print(f"从Excel文件 {excel_path} 中读取到 {len(df)} 行数据")
        # 打印列名用于调试
        print("Excel列名:", df.columns.tolist())
        
        # 检查是否包含所需的列
        required_columns = ['比赛时间', '球队A', '球队A队标链接', '球队B', '球队B队标链接', '文案']
        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            raise Exception(f"Excel文件缺少必要的列: {missing_columns}")
        
        # 提取所需列信息
        matches_data = []
        for index, row in df.iterrows():
            # 跳过完全空的行（通常是表头后的空行）
            if pd.isna(row['比赛时间']) and pd.isna(row['球队A']) and pd.isna(row['球队B']):
                print(f"跳过空行: 第 {index+1} 行")
                continue
                
            # 处理可能的NaN值并去除首尾空格
            match_time = row['比赛时间']
            team_a = row['球队A']
            team_a_logo = row['球队A队标链接']
            team_b = row['球队B']
            team_b_logo = row['球队B队标链接']
            copywriting = row['文案']
            
            # 处理NaN值并去除首尾空格
            match_time = '' if pd.isna(match_time) else str(match_time).strip()
            team_a = '' if pd.isna(team_a) else str(team_a).strip()
            team_a_logo = '' if pd.isna(team_a_logo) else str(team_a_logo).strip()
            team_b = '' if pd.isna(team_b) else str(team_b).strip()
            team_b_logo = '' if pd.isna(team_b_logo) else str(team_b_logo).strip()
            copywriting = '' if pd.isna(copywriting) else str(copywriting).strip()
            
            # 跳过关键字段都为空的行
            if not match_time and not team_a and not team_b:
                print(f"跳过关键字段为空的行: 第 {index+1} 行")
                continue
            
            match_info = {
                'match_time': match_time,          # 日期+时间
                'team_a': team_a,                  # 主队名称
                'team_a_logo': team_a_logo,        # 主队队标链接
                'team_b': team_b,                  # 客队名称
                'team_b_logo': team_b_logo,        # 客队队标链接
                'copywriting': copywriting         # 文案
            }
            matches_data.append(match_info)
            print(f"读取到第 {index+1} 行比赛数据: 时间='{match_time}', 球队A='{team_a}', 球队B='{team_b}'")  # 调试信息
        
        print(f"总共读取到 {len(matches_data)} 条比赛数据")
        return matches_data
    except Exception as e:
        raise Exception(f"读取Excel文件失败: {str(e)}")


def base64_to_temp_image(base64_string, prefix="team_logo"):
    """
    将base64字符串转换为临时图片文件并保存
    
    Args:
        base64_string (str): base64编码的图片数据
        prefix (str): 文件名前缀
        
    Returns:
        str: 保存的文件路径，如果失败则返回None
    """
    try:
        # 检查是否是有效的base64字符串
        if not base64_string or not base64_string.startswith('data:image'):
            return None
            
        # 解析base64数据
        header, encoded_data = base64_string.split(',', 1)
        file_extension = header.split('/')[1].split(';')[0]
        
        # 解码base64数据
        image_data = base64.b64decode(encoded_data)
        
        # 生成临时文件名
        filename = f"{prefix}_{random.randint(1000, 9999)}.{file_extension}"
        temp_dir = os.path.join(os.getcwd(), 'temp')
        os.makedirs(temp_dir, exist_ok=True)
        file_path = os.path.join(temp_dir, filename)
        
        # 保存图片文件
        with open(file_path, 'wb') as f:
            f.write(image_data)
            
        return file_path
    except Exception as e:
        print(f"Base64转换为图片文件失败: {e}")
        return None


def add_text_and_logos_to_video(video_path, output_path, match_info):
    """
    在视频上添加文本和队伍图标
    
    Args:
        video_path (str): 原始视频路径
        output_path (str): 输出视频路径
        match_info (dict): 比赛信息，包含以下字段:
            - team_a: 球队A名称
            - team_b: 球队B名称
            - match_time: 比赛时间
            - copywriting: 文案
            - team_a_logo: 球队A图标(base64格式)
            - team_b_logo: 球队B图标(base64格式)
        
    Returns:
        bool: 是否成功
    """
    if not MOVIEPY_AVAILABLE or not PIL_AVAILABLE:
        print("缺少必要的依赖库，请检查moviepy和Pillow是否正确安装")
        return False
        
    try:
        # 检查视频文件是否存在
        if not os.path.exists(video_path):
            print(f"视频文件不存在: {video_path}")
            return False
            
        # 加载视频
        video = VideoFileClip(video_path)
        width, height = video.size
        
        # 创建文本剪辑
        text_clips = []
        
        # 添加比赛标题 (球队A vs 球队B)
        title_text = f"{match_info['team_a']} vs {match_info['team_b']}"
        title_clip = TextClip(title_text, fontsize=80, color='white', stroke_color='black', stroke_width=2, font='Arial-Bold')
        title_clip = title_clip.with_position(('center', height // 8)).with_duration(video.duration)
        text_clips.append(title_clip)
        
        # 添加比赛时间
        time_text = match_info['match_time']
        time_clip = TextClip(time_text, fontsize=60, color='yellow', stroke_color='black', stroke_width=1, font='Arial-Bold')
        time_clip = time_clip.with_position(('center', height // 4)).with_duration(video.duration)
        text_clips.append(time_clip)
        
        # 添加文案
        copywriting = match_info['copywriting']
        copywriting_clip = TextClip(copywriting, fontsize=40, color='white', stroke_color='black', stroke_width=1, font='Arial')
        copywriting_clip = copywriting_clip.with_position(('center', height * 3 // 4)).with_duration(video.duration)
        text_clips.append(copywriting_clip)
        
        # 处理队伍图标
        logo_clips = []
        logo_size = 120
        
        if 'team_a_logo' in match_info and match_info['team_a_logo']:
            team_a_logo_path = base64_to_temp_image(match_info['team_a_logo'], "team_a_logo")
            if team_a_logo_path:
                try:
                    team_a_logo_clip = ImageClip(team_a_logo_path).with_duration(video.duration)
                    team_a_logo_clip = team_a_logo_clip.resize((logo_size, logo_size))
                    # 添加一些动画效果
                    team_a_logo_clip = team_a_logo_clip.with_position(
                        lambda t: (width // 4 - logo_size // 2, height * 5 // 8)
                    )
                    logo_clips.append(team_a_logo_clip)
                except Exception as e:
                    print(f"处理队伍A视频图标时出错: {e}")
        
        if 'team_b_logo' in match_info and match_info['team_b_logo']:
            team_b_logo_path = base64_to_temp_image(match_info['team_b_logo'], "team_b_logo")
            if team_b_logo_path:
                try:
                    team_b_logo_clip = ImageClip(team_b_logo_path).with_duration(video.duration)
                    team_b_logo_clip = team_b_logo_clip.resize((logo_size, logo_size))
                    # 添加一些动画效果
                    team_b_logo_clip = team_b_logo_clip.with_position(
                        lambda t: (width * 3 // 4 - logo_size // 2, height * 5 // 8)
                    )
                    logo_clips.append(team_b_logo_clip)
                except Exception as e:
                    print(f"处理队伍B视频图标时出错: {e}")
        
        # 合成最终视频
        final_clips = [video] + text_clips + logo_clips
        final_video = CompositeVideoClip(final_clips)
        
        # 添加淡入淡出效果
        final_video = final_video.fadein(0.5).fadeout(0.5)
        
        # 确保输出目录存在
        output_dir = os.path.dirname(output_path)
        if output_dir:
            os.makedirs(output_dir, exist_ok=True)
        
        # 输出视频
        print(f"正在生成视频: {output_path}")
        final_video.write_videofile(output_path, codec='libx264', audio_codec='aac')
        
        # 清理临时文件
        if 'team_a_logo_path' in locals() and team_a_logo_path and os.path.exists(team_a_logo_path):
            os.remove(team_a_logo_path)
        if 'team_b_logo_path' in locals() and team_b_logo_path and os.path.exists(team_b_logo_path):
            os.remove(team_b_logo_path)
            
        return True
    except Exception as e:
        print(f"在视频上添加文本和图标时出错: {e}")
        return False


def process_videos_from_excel(excel_path, videos_dir, output_dir):
    """
    根据Excel数据处理视频文件
    
    Args:
        excel_path (str): Excel文件路径
        videos_dir (str): 视频文件所在目录
        output_dir (str): 输出视频目录
    """
    if not MOVIEPY_AVAILABLE or not PIL_AVAILABLE:
        print("缺少必要的依赖库，请检查moviepy和Pillow是否正确安装")
        return False
        
    try:
        # 读取Excel数据
        matches_data = read_excel_data(excel_path)
        if not matches_data:
            print("没有读取到比赛数据")
            return False
            
        # 确保输出目录存在
        os.makedirs(output_dir, exist_ok=True)
        
        # 处理每个比赛数据
        for i, match_info in enumerate(matches_data):
            try:
                print(f"正在处理第 {i+1}/{len(matches_data)} 项: {match_info['team_a']} vs {match_info['team_b']}")
                
                # 构造视频文件名（根据实际文件命名规则调整）
                # 这里假设视频文件名包含球队名称和时间信息
                team_a = match_info['team_a']
                team_b = match_info['team_b']
                match_time = match_info['match_time'].replace(':', '-')
                
                # 清理文件名中的非法字符
                safe_team_a = "".join(c for c in team_a if c.isalnum() or c in (' ', '-', '_')).rstrip()
                safe_team_b = "".join(c for c in team_b if c.isalnum() or c in (' ', '-', '_')).rstrip()
                
                # 尝试几种可能的视频文件名格式
                possible_filenames = [
                    f"*{safe_team_a}*{safe_team_b}*.mp4",
                    f"*{safe_team_a}*vs*{safe_team_b}*.mp4",
                    f"*{safe_team_b}*vs*{safe_team_a}*.mp4",
                    f"*{safe_team_a}*{safe_team_b}*.mp4",
                    f"*{match_time}*{safe_team_a}*{safe_team_b}*.mp4",
                    f"*{safe_team_a}*{safe_team_b}*{match_time}*.mp4"
                ]
                
                video_path = None
                for pattern in possible_filenames:
                    matched_files = list(Path(videos_dir).glob(pattern))
                    if matched_files:
                        video_path = str(matched_files[0])
                        break
                
                if not video_path or not os.path.exists(video_path):
                    print(f"  未找到匹配的视频文件")
                    continue
                
                # 构造输出文件路径
                output_filename = f"{safe_team_a}_vs_{safe_team_b}_with_text.mp4"
                output_path = os.path.join(output_dir, output_filename)
                
                # 在视频上添加文本和图标
                success = add_text_and_logos_to_video(video_path, output_path, match_info)
                
                if success:
                    print(f"  视频处理成功: {output_path}")
                else:
                    print(f"  视频处理失败")
                    
            except Exception as e:
                print(f"  处理过程中发生错误: {str(e)}")
                
        return True
    except Exception as e:
        print(f"处理Excel数据时出错: {e}")
        return False


def main():
    """
    主函数 - 演示如何使用process_videos_from_excel函数
    """
    # 示例Excel文件路径（请根据实际情况修改）
    excel_path = r"path/to/your/excel.xlsx"
    
    # 视频文件所在目录（请根据实际情况修改）
    videos_dir = r"\\Fw-152\e\媒体部素材\海报类\海报推单产出_TEST\videos"
    
    # 输出视频目录
    output_dir = r"\\Fw-152\e\媒体部素材\海报类\海报推单产出_TEST\videos_with_text"
    
    print(f"Excel文件路径: {excel_path}")
    print(f"视频目录: {videos_dir}")
    print(f"输出目录: {output_dir}")
    
    # 根据Excel数据处理视频文件
    success = process_videos_from_excel(excel_path, videos_dir, output_dir)
    
    if success:
        print("所有视频处理完成!")
    else:
        print("视频处理失败!")


if __name__ == "__main__":
    main()